package com.chenailin.www.daoimpl;

import com.chenailin.www.dao.RatingDAO;
import com.chenailin.www.pojo.Rating;
import com.chenailin.www.util.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class RatingDAOImpl implements RatingDAO {

    // 添加评价
    @Override
    public boolean addRating(Rating rating) throws SQLException {
        String sql = "INSERT INTO ratings (doctor_id, user_id, department_id, service_quality, environment, comment) VALUES (?, ?, ?, ?, ?, ?)";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, rating.getDoctorId());
            ps.setInt(2, rating.getStuId());
            ps.setInt(3, rating.getDepartmentId());
            ps.setInt(4, rating.getServiceQuality());
            ps.setInt(5, rating.getEnvironment());
            ps.setString(6, rating.getComment());
            //受影响的行数
            int affectedRows = ps.executeUpdate();
            return affectedRows > 0;
        } catch (SQLException e) {
            System.err.println("数据库操作失败: " + e.getMessage());
            return false;
        }

    }

    // 查询医生评价
    @Override
    public List<Rating> getRatingsByDoctor(int doctorId) {
        List<Rating> ratings = new ArrayList<>();
        String sql = "SELECT * FROM ratings WHERE doctor_id = ?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, doctorId);
            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    ratings.add(new Rating(rs.getString("comment"),
                            rs.getInt("department_id"),
                            rs.getInt("doctor_id"),
                            rs.getInt("environment"),
                            rs.getInt("service_quality"),
                            rs.getInt("user_id")));
                }
            }
            return ratings;
        } catch (SQLException e) {
            System.err.println("数据库操作失败: " + e.getMessage());
            return null;
        }
    }

    // 查询科室评价
    @Override
    public List<Rating> getRatingsByDepartment(int departmentId) {
        List<Rating> ratings = new ArrayList<>();
        String sql = "SELECT * FROM ratings WHERE department_id = ?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, departmentId);
            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    ratings.add(new Rating(rs.getString("comment"),
                            rs.getInt("department_id"),
                            rs.getInt("doctor_id"),
                            rs.getInt("environment"),
                            rs.getInt("service_quality"),
                            rs.getInt("user_id")));
                }
            }
            return ratings;
        } catch (SQLException e) {
            System.err.println("数据库操作失败: " + e.getMessage());
            return null;
        }
    }

    // 获取平均服务评分
    @Override
    public double getAverageRatingByDoctor(int doctorId) {
        String sql = "SELECT AVG(service_quality) AS avg_rating FROM ratings WHERE doctor_id = ?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, doctorId);
            try (ResultSet rs = ps.executeQuery()) {
                if (rs.next()) {
                    return rs.getDouble("avg_rating");
                }
                return 0.0; // 无评分时返回0
            }
        } catch (SQLException e) {
            System.err.println("数据库操作失败: " + e.getMessage());
            return 0.0;
        }
    }

    // 获取平均科室评分
    @Override
    public double getAverageDepartmentRating(int departmentId) {
        String sql = "SELECT AVG(service_quality) AS avg_rating FROM ratings WHERE department_id = ?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, departmentId);
            try (ResultSet rs = ps.executeQuery()) {
                if (rs.next()) {
                    return rs.getDouble("avg_rating");
                }
                return 0.0; // 无评分时返回0
            }
        } catch (SQLException e) {
            System.err.println("数据库操作失败: " + e.getMessage());
            return 0.0;
        }
    }
}
